﻿using System;
using System.Collections.Generic;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Net.Http.Headers;
using System.Security.Cryptography;
using System.Text;
using System.Text.Json.Serialization;
using System.Text.Json;
using System.Threading.Tasks;
using DocumentFormat.OpenXml.InkML;
using Microsoft.Extensions.Hosting;
using MT.Enterprise.Utils.Extensions;
using Newtonsoft.Json;
using NP.BPMReportPlatform.BPMPlatform;
using NP.BPMReportPlatform.DBHelper;
using NP.BPMReportPlatform.Entities.BPM_Business;
using NP.BPMReportPlatform.Entities.BPM_Business.Resume;
using NP.BPMReportPlatform.Entities.BPM_Engine;
using NP.BPMReportPlatform.Entities.BPM_Platform;
using NP.BPMReportPlatform.Resume.Dtos;
using NP.BPMReportPlatform.TalentProfile;
using NP.BPMReportPlatform.TalentProfile.Dto;
using NP.BPMReportPlatform.Utils;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using Renci.SshNet;
using SqlSugar;
using Volo.Abp.Application.Dtos;
using Volo.Abp.Clients;
using Aliyun.OSS.Common;
using Aliyun.OSS;
using NPOI.HPSF;
using Org.BouncyCastle.Crypto.Tls;
using Aliyun.OSS.Util;
using Microsoft.Extensions.Configuration;
using System.Text.RegularExpressions;
using NP.BPMReportPlatform.CourseTrain.Dtos;
using NP.BPMReportPlatform.UserTrial.Dto;
using DocumentFormat.OpenXml.Office2010.Excel;
using Serilog;

namespace NP.BPMReportPlatform.Resume
{
    /// <summary>
    /// 试用期服务
    /// </summary>
    public class ResumeService : BPMReportPlatformAppService, IResumeService
    {
        #region Variables

        private readonly SqlSugarClient _db;
        private readonly SqlSugarClient _dbPlatform;
        private readonly IBPMUtilsAppService _bPMUtilsApp;
        private readonly IBPMPlatformAppService _iBPMPlatformApp;
        private readonly SqlSugarClient _dbEngine;
        private readonly string _accessKeyId;
        private readonly string _accessKeySecret;
        private readonly string _bucketName;
        private readonly string _endpoint;
        private readonly string _ftpServer;
        private readonly string _ftpUsername;
        private readonly string _ftpPassword;
        private readonly string _localFilePath;
        private readonly ILogger _logger;
        #endregion

        #region Constructor

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="sqlSugarAppService"></param>
        public ResumeService(ISqlSugarAppService sqlSugarAppService, IBPMUtilsAppService bPMUtilsApp
            , IBPMPlatformAppService iBPMPlatformApp, IConfiguration configuration, ILogger logger)
        {
            _db = sqlSugarAppService.GetBusinessInstance();
            _dbPlatform = sqlSugarAppService.GetPlatformInstance();
            _dbEngine = sqlSugarAppService.GetEngineInstance();
            _bPMUtilsApp = bPMUtilsApp;
            _iBPMPlatformApp = iBPMPlatformApp;
            _ftpServer = configuration["OSS:FtpServer"];
            _ftpUsername = configuration["OSS:FtpUsername"];
            _ftpPassword = configuration["OSS:FtpPassword"];
            _localFilePath = configuration["OSS:LocalFilePath"];
            _accessKeyId = configuration["OSS:AccessKeyId"];
            _accessKeySecret = configuration["OSS:AccessKeySecret"];
            _bucketName = configuration["OSS:BucketName"];
            _endpoint = configuration["OSS:Endpoint"];
            _logger = logger;
        }

        #endregion

        /// <summary>
        /// 查询简历明细
        /// </summary>
        /// <param name="input"></param>
        /// <returns>简历明细列表</returns>
        public async Task<PagedResultDto<ResumeInfoEntity>> GetResumeInfoListResult(GetResumeInfoInput input)
        {
            RefAsync<int> totalCount = 0;
            // 权限过滤
            var currentWorkNumber = CurrentUser != null && CurrentUser.UserName != null ? CurrentUser.UserName : string.Empty;
            var result = new PagedResultPowerDto<ResumeInfoEntity>();
            var listQueryable = _db.Queryable<ResumeInfoEntity>()
                  .WhereIF(!string.IsNullOrEmpty(input.Name), t => t.Name.Contains(input.Name))
                  .WhereIF(!string.IsNullOrEmpty(input.MobilePhone), t => t.MobilePhone.Contains(input.MobilePhone))
                  .WhereIF(!string.IsNullOrEmpty(input.Email), t => t.Email.Contains(input.Email))
                  .WhereIF(input.WorkExperienceYears != null, t => t.WorkExperienceYears >= input.WorkExperienceYears)
                  .WhereIF(!string.IsNullOrEmpty(input.EducationLevel), t => t.EducationLevel == input.EducationLevel)
                  .WhereIF(!string.IsNullOrEmpty(input.Major), t => t.Major == input.Major)
                  .WhereIF(!string.IsNullOrEmpty(input.ExpectedSalary), t => t.ExpectedSalary.Contains(input.ExpectedSalary))
                  .OrderBy(t => new { t.ID }, OrderByType.Asc);

            var listViewDto = await listQueryable.ToPageListAsync(input.PageIndex, input.PageSize, totalCount);

            result.TotalCount = totalCount;
            result.Items = listViewDto;
            return result;
        }

        /// <summary>
        /// 获取OSS下载链接
        /// </summary>
        /// <param name="id">id</param>
        /// <param name="type">附件序号</param>
        /// <returns></returns>
        public string GetDownloadUri(string id, int type)
        {
            string uri = string.Empty;
            var client = new OssClient(_endpoint, _accessKeyId, _accessKeySecret);
            try
            {
                string objectKey = string.Empty;
                var fileInfo = _db.Queryable<ResumeInfoEntity>().Where(t => t.ID == id).Select(t => new { t.SyncDate, t.Attachment1, t.Attachment2, t.Attachment3, t.Attachment4, t.Attachment5 }).First();
                switch (type)
                {
                    case 1:
                        objectKey = $"{fileInfo.SyncDate}/{fileInfo.Attachment1}";
                        break;
                    case 2:
                        objectKey = $"{fileInfo.SyncDate}/{fileInfo.Attachment2}";
                        break;
                    case 3:
                        objectKey = $"{fileInfo.SyncDate}/{fileInfo.Attachment3}";
                        break;
                    case 4:
                        objectKey = $"{fileInfo.SyncDate}/{fileInfo.Attachment4}";
                        break;
                    case 5:
                        objectKey = $"{fileInfo.SyncDate}/{fileInfo.Attachment5}";
                        break;
                }


                // 生成预签名URL
                var presignedUrl = client.GeneratePresignedUri(_bucketName, objectKey, DateTime.Now.AddHours(1));
                uri = presignedUrl.ToString();
                Console.WriteLine($"Pre-signed URL: {presignedUrl}");
            }
            catch (OssException ex)
            {
                // 处理OSS异常
                Console.WriteLine($"Error: {ex.ErrorCode}");
                Console.WriteLine($"Reason: {ex.Message}");
                Console.WriteLine($"RequestId: {ex.RequestId}");
                Console.WriteLine($"HostId: {ex.HostId}");
            }
            catch (Exception ex)
            {
                // 处理其他异常
                Console.WriteLine($"Error: {ex.Message}");
            }

            return uri;
        }

        /// <summary>
        /// 从ftp同步文件
        /// </summary>
        /// <returns></returns>
        public async Task SyncResumeFiles(string filPath = "")
        {
            string _remoteFilePath = string.Empty;
            if (filPath.IsNullOrEmpty())
            {
                _remoteFilePath = _dbPlatform.Queryable<Dictionaries>().Where(t => t.TypeCode == "SyncResumeDate").First()?.Value;
            }
            else
            {
                _remoteFilePath = filPath;
            }
            try
            {
                Dictionary<string, string> replaceFiles = new Dictionary<string, string>();
                List<string> excelNames = new List<string>();
                FtpTool ftp = new FtpTool(_ftpServer, 8888, _ftpUsername, _ftpPassword);
                var files = ftp.GetSftpAllFile("/" + _remoteFilePath);
                foreach (var file in files)
                {
                    if (!file.Attributes.IsDirectory)
                    {
                        var result = ftp.SftpDownloadFile(file.FullName, _localFilePath);
                        if (result.Item1.Any())
                        {
                            foreach (var item in result.Item1)
                            {
                                replaceFiles.Add(item.Key, item.Value);
                            }
                        }

                        excelNames.AddRange(result.Item2);
                    }
                }

                // 解析丢给后台做
                _ = Task.Run(() =>
                {
                    AnalysisResumeFiles(replaceFiles, excelNames, _localFilePath, _remoteFilePath);
                });
            }
            catch (Exception ex)
            {
                TalentProfileDescribeEntity talentProfileDescribeEntity = new TalentProfileDescribeEntity();
                talentProfileDescribeEntity.Id = Guid.NewGuid().ToString();
                talentProfileDescribeEntity.TalentProfileId = "OSSFailed";
                talentProfileDescribeEntity.Content = $"从ftp同步文件失败，同步日期{filPath}，错误原因：{JsonConvert.SerializeObject(ex)}";
                _db.Insertable(talentProfileDescribeEntity).ExecuteCommand();
            }
            finally
            {
                if (filPath.IsNullOrEmpty())
                {
                    DateTime date = DateTime.Parse(_remoteFilePath);
                    string newDate = date.AddDays(1).ToString("yyyy-MM-dd");
                    await _dbPlatform.Updateable<Dictionaries>().SetColumns(t => t.Value == newDate).Where(t => t.TypeCode == "SyncResumeDate").ExecuteCommandAsync();
                }
            }
        }

        /// <summary>
        /// 解析EXCEL，并将数据插入数据库
        /// </summary>
        /// <param name="replaceFiles">非法字符替换文件名</param>
        /// <param name="excelNames">excel名</param>
        /// <param name="localFilePath">本地路径</param>
        private async void AnalysisResumeFiles(Dictionary<string, string> replaceFiles, List<string> excelNames, string localFilePath, string remoteFilePath)
        {
            foreach (var name in excelNames)
            {
                try
                {
                    string excelpath = $"{localFilePath}/{remoteFilePath}/{name}";
                    using (FileStream fs = new FileStream(excelpath, FileMode.Open, FileAccess.Read))
                    {
                        IWorkbook workbook = WorkbookFactory.Create(fs);
                        ISheet sheet = workbook.GetSheetAt(0);
                        List<ResumeInfoEntity> resumeInfos = new List<ResumeInfoEntity>();
                        for (int i = 2; i < sheet.LastRowNum; i++)
                        {
                            IRow row = sheet.GetRow(i);
                            ResumeInfoEntity resumeInfo = new ResumeInfoEntity();
                            resumeInfo.ID = ConvertSheetData(row.GetCell(0));
                            resumeInfo.Name = ConvertSheetData(row.GetCell(1));
                            resumeInfo.Gender = ConvertSheetData(row.GetCell(2));
                            resumeInfo.BirthDate = ConvertSheetData(row.GetCell(3));
                            resumeInfo.MaritalStatus = ConvertSheetData(row.GetCell(4));
                            resumeInfo.IDNumber = ConvertSheetData(row.GetCell(5));
                            resumeInfo.CurrentResidence = ConvertSheetData(row.GetCell(6));
                            resumeInfo.HouseholdRegistration = ConvertSheetData(row.GetCell(7));
                            resumeInfo.WorkExperienceYears = row.GetCell(8) != null ? Convert.ToInt16(ConvertSheetData(row.GetCell(8))) : null;
                            resumeInfo.EducationLevel = ConvertSheetData(row.GetCell(9));
                            resumeInfo.GraduationTime = ConvertSheetData(row.GetCell(10));
                            resumeInfo.GraduationInstitution = ConvertSheetData(row.GetCell(11));
                            resumeInfo.Major = ConvertSheetData(row.GetCell(12));
                            resumeInfo.MobilePhone = ConvertSheetData(row.GetCell(13));
                            resumeInfo.Email = ConvertSheetData(row.GetCell(14));
                            resumeInfo.CorrespondenceAddress = ConvertSheetData(row.GetCell(15));
                            resumeInfo.SelfEvaluation = ConvertSheetData(row.GetCell(16));
                            resumeInfo.ExpectedJobNature = ConvertSheetData(row.GetCell(17));
                            resumeInfo.ExpectedWorkLocation = ConvertSheetData(row.GetCell(18));
                            resumeInfo.ExpectedOccupation = ConvertSheetData(row.GetCell(19));
                            resumeInfo.ExpectedIndustry = ConvertSheetData(row.GetCell(20));
                            resumeInfo.ExpectedSalary = ConvertSheetData(row.GetCell(21));
                            resumeInfo.CurrentSalary = ConvertSheetData(row.GetCell(22));
                            resumeInfo.CurrentJobSearchStatus = ConvertSheetData(row.GetCell(23));
                            #region 教育经历
                            EducationDto educationDto = new EducationDto();
                            educationDto.Education1StartTime = ConvertSheetData(row.GetCell(24));
                            educationDto.Education1EndTime = ConvertSheetData(row.GetCell(25));
                            educationDto.Education1School = ConvertSheetData(row.GetCell(26));
                            educationDto.Education1Degree = ConvertSheetData(row.GetCell(27));
                            educationDto.Education1Major = ConvertSheetData(row.GetCell(28));
                            educationDto.Education2StartTime = ConvertSheetData(row.GetCell(29));
                            educationDto.Education2EndTime = ConvertSheetData(row.GetCell(30));
                            educationDto.Education2School = ConvertSheetData(row.GetCell(31));
                            educationDto.Education2Degree = ConvertSheetData(row.GetCell(32));
                            educationDto.Education2Major = ConvertSheetData(row.GetCell(33));
                            educationDto.Education3StartTime = ConvertSheetData(row.GetCell(34));
                            educationDto.Education3EndTime = ConvertSheetData(row.GetCell(35));
                            educationDto.Education3School = ConvertSheetData(row.GetCell(36));
                            educationDto.Education3Degree = ConvertSheetData(row.GetCell(37));
                            educationDto.Education3Major = ConvertSheetData(row.GetCell(38));
                            educationDto.Education4StartTime = ConvertSheetData(row.GetCell(39));
                            educationDto.Education4EndTime = ConvertSheetData(row.GetCell(40));
                            educationDto.Education4School = ConvertSheetData(row.GetCell(41));
                            educationDto.Education4Degree = ConvertSheetData(row.GetCell(42));
                            educationDto.Education4Major = ConvertSheetData(row.GetCell(43));
                            educationDto.Education5StartTime = ConvertSheetData(row.GetCell(44));
                            educationDto.Education5EndTime = ConvertSheetData(row.GetCell(45));
                            educationDto.Education5School = ConvertSheetData(row.GetCell(46));
                            educationDto.Education5Degree = ConvertSheetData(row.GetCell(47));
                            educationDto.Education5Major = ConvertSheetData(row.GetCell(48));
                            educationDto.Education6StartTime = ConvertSheetData(row.GetCell(49));
                            educationDto.Education6EndTime = ConvertSheetData(row.GetCell(50));
                            educationDto.Education6School = ConvertSheetData(row.GetCell(51));
                            educationDto.Education6Degree = ConvertSheetData(row.GetCell(52));
                            educationDto.Education6Major = ConvertSheetData(row.GetCell(53));
                            educationDto.Education7StartTime = ConvertSheetData(row.GetCell(54));
                            educationDto.Education7EndTime = ConvertSheetData(row.GetCell(55));
                            educationDto.Education7School = ConvertSheetData(row.GetCell(56));
                            educationDto.Education7Degree = ConvertSheetData(row.GetCell(57));
                            educationDto.Education7Major = ConvertSheetData(row.GetCell(58));
                            educationDto.Education8StartTime = ConvertSheetData(row.GetCell(59));
                            educationDto.Education8EndTime = ConvertSheetData(row.GetCell(60));
                            educationDto.Education8School = ConvertSheetData(row.GetCell(61));
                            educationDto.Education8Degree = ConvertSheetData(row.GetCell(62));
                            educationDto.Education8Major = ConvertSheetData(row.GetCell(63));
                            educationDto.Education9StartTime = ConvertSheetData(row.GetCell(64));
                            educationDto.Education9EndTime = ConvertSheetData(row.GetCell(65));
                            educationDto.Education9School = ConvertSheetData(row.GetCell(66));
                            educationDto.Education9Degree = ConvertSheetData(row.GetCell(67));
                            educationDto.Education9Major = ConvertSheetData(row.GetCell(68));
                            educationDto.Education10StartTime = ConvertSheetData(row.GetCell(69));
                            educationDto.Education10EndTime = ConvertSheetData(row.GetCell(70));
                            educationDto.Education10School = ConvertSheetData(row.GetCell(71));
                            educationDto.Education10Degree = ConvertSheetData(row.GetCell(72));
                            educationDto.Education10Major = ConvertSheetData(row.GetCell(73));
                            educationDto.Education11StartTime = ConvertSheetData(row.GetCell(74));
                            educationDto.Education11EndTime = ConvertSheetData(row.GetCell(75));
                            educationDto.Education11School = ConvertSheetData(row.GetCell(76));
                            educationDto.Education11Degree = ConvertSheetData(row.GetCell(77));
                            educationDto.Education11Major = ConvertSheetData(row.GetCell(78));
                            educationDto.Education12StartTime = ConvertSheetData(row.GetCell(79));
                            educationDto.Education12EndTime = ConvertSheetData(row.GetCell(80));
                            educationDto.Education12School = ConvertSheetData(row.GetCell(81));
                            educationDto.Education12Degree = ConvertSheetData(row.GetCell(82));
                            educationDto.Education12Major = ConvertSheetData(row.GetCell(83));
                            resumeInfo.Education = JsonConvert.SerializeObject(educationDto);
                            #endregion
                            resumeInfo.RewardActivities = ConvertSheetData(row.GetCell(84));

                            #region 工作经历
                            WorkExperienceDto workExperience = new WorkExperienceDto();
                            workExperience.WorkExperience1StartTime = ConvertSheetData(row.GetCell(85));
                            workExperience.WorkExperience1EndTime = ConvertSheetData(row.GetCell(86));
                            workExperience.WorkExperience1CompanyName = ConvertSheetData(row.GetCell(87));
                            workExperience.WorkExperience1PositionName = ConvertSheetData(row.GetCell(88));
                            workExperience.WorkExperience1AnnualSalary = ConvertSheetData(row.GetCell(89));
                            workExperience.WorkExperience1IndustryCategory = ConvertSheetData(row.GetCell(90));
                            workExperience.WorkExperience1JobDescription = ConvertSheetData(row.GetCell(91));
                            workExperience.WorkExperience1Department = ConvertSheetData(row.GetCell(92));
                            workExperience.WorkExperience1ResignationReason = ConvertSheetData(row.GetCell(93));
                            workExperience.WorkExperience2StartTime = ConvertSheetData(row.GetCell(94));
                            workExperience.WorkExperience2EndTime = ConvertSheetData(row.GetCell(95));
                            workExperience.WorkExperience2CompanyName = ConvertSheetData(row.GetCell(96));
                            workExperience.WorkExperience2PositionName = ConvertSheetData(row.GetCell(97));
                            workExperience.WorkExperience2AnnualSalary = ConvertSheetData(row.GetCell(98));
                            workExperience.WorkExperience2IndustryCategory = ConvertSheetData(row.GetCell(99));
                            workExperience.WorkExperience2JobDescription = ConvertSheetData(row.GetCell(100));
                            workExperience.WorkExperience2Department = ConvertSheetData(row.GetCell(101));
                            workExperience.WorkExperience2ResignationReason = ConvertSheetData(row.GetCell(102));
                            workExperience.WorkExperience3StartTime = ConvertSheetData(row.GetCell(103));
                            workExperience.WorkExperience3EndTime = ConvertSheetData(row.GetCell(104));
                            workExperience.WorkExperience3CompanyName = ConvertSheetData(row.GetCell(105));
                            workExperience.WorkExperience3PositionName = ConvertSheetData(row.GetCell(106));
                            workExperience.WorkExperience3AnnualSalary = ConvertSheetData(row.GetCell(107));
                            workExperience.WorkExperience3IndustryCategory = ConvertSheetData(row.GetCell(108));
                            workExperience.WorkExperience3JobDescription = ConvertSheetData(row.GetCell(109));
                            workExperience.WorkExperience3Department = ConvertSheetData(row.GetCell(110));
                            workExperience.WorkExperience3ResignationReason = ConvertSheetData(row.GetCell(111));
                            workExperience.WorkExperience4StartTime = ConvertSheetData(row.GetCell(112));
                            workExperience.WorkExperience4EndTime = ConvertSheetData(row.GetCell(113));
                            workExperience.WorkExperience4CompanyName = ConvertSheetData(row.GetCell(114));
                            workExperience.WorkExperience4PositionName = ConvertSheetData(row.GetCell(115));
                            workExperience.WorkExperience4AnnualSalary = ConvertSheetData(row.GetCell(116));
                            workExperience.WorkExperience4IndustryCategory = ConvertSheetData(row.GetCell(117));
                            workExperience.WorkExperience4JobDescription = ConvertSheetData(row.GetCell(118));
                            workExperience.WorkExperience4Department = ConvertSheetData(row.GetCell(119));
                            workExperience.WorkExperience4ResignationReason = ConvertSheetData(row.GetCell(120));
                            workExperience.WorkExperience5StartTime = ConvertSheetData(row.GetCell(121));
                            workExperience.WorkExperience5EndTime = ConvertSheetData(row.GetCell(122));
                            workExperience.WorkExperience5CompanyName = ConvertSheetData(row.GetCell(123));
                            workExperience.WorkExperience5PositionName = ConvertSheetData(row.GetCell(124));
                            workExperience.WorkExperience5AnnualSalary = ConvertSheetData(row.GetCell(125));
                            workExperience.WorkExperience5IndustryCategory = ConvertSheetData(row.GetCell(126));
                            workExperience.WorkExperience5JobDescription = ConvertSheetData(row.GetCell(127));
                            workExperience.WorkExperience5Department = ConvertSheetData(row.GetCell(128));
                            workExperience.WorkExperience5ResignationReason = ConvertSheetData(row.GetCell(129));
                            workExperience.WorkExperience6StartTime = ConvertSheetData(row.GetCell(130));
                            workExperience.WorkExperience6EndTime = ConvertSheetData(row.GetCell(131));
                            workExperience.WorkExperience6CompanyName = ConvertSheetData(row.GetCell(132));
                            workExperience.WorkExperience6PositionName = ConvertSheetData(row.GetCell(133));
                            workExperience.WorkExperience6AnnualSalary = ConvertSheetData(row.GetCell(134));
                            workExperience.WorkExperience6IndustryCategory = ConvertSheetData(row.GetCell(135));
                            workExperience.WorkExperience6JobDescription = ConvertSheetData(row.GetCell(136));
                            workExperience.WorkExperience6Department = ConvertSheetData(row.GetCell(137));
                            workExperience.WorkExperience6ResignationReason = ConvertSheetData(row.GetCell(138));
                            workExperience.WorkExperience7StartTime = ConvertSheetData(row.GetCell(139));
                            workExperience.WorkExperience7EndTime = ConvertSheetData(row.GetCell(140));
                            workExperience.WorkExperience7CompanyName = ConvertSheetData(row.GetCell(141));
                            workExperience.WorkExperience7PositionName = ConvertSheetData(row.GetCell(142));
                            workExperience.WorkExperience7AnnualSalary = ConvertSheetData(row.GetCell(143));
                            workExperience.WorkExperience7IndustryCategory = ConvertSheetData(row.GetCell(144));
                            workExperience.WorkExperience7JobDescription = ConvertSheetData(row.GetCell(145));
                            workExperience.WorkExperience7Department = ConvertSheetData(row.GetCell(146));
                            workExperience.WorkExperience7ResignationReason = ConvertSheetData(row.GetCell(147));
                            workExperience.WorkExperience8StartTime = ConvertSheetData(row.GetCell(148));
                            workExperience.WorkExperience8EndTime = ConvertSheetData(row.GetCell(149));
                            workExperience.WorkExperience8CompanyName = ConvertSheetData(row.GetCell(150));
                            workExperience.WorkExperience8PositionName = ConvertSheetData(row.GetCell(151));
                            workExperience.WorkExperience8AnnualSalary = ConvertSheetData(row.GetCell(152));
                            workExperience.WorkExperience8IndustryCategory = ConvertSheetData(row.GetCell(153));
                            workExperience.WorkExperience8JobDescription = ConvertSheetData(row.GetCell(154));
                            workExperience.WorkExperience8Department = ConvertSheetData(row.GetCell(155));
                            workExperience.WorkExperience8ResignationReason = ConvertSheetData(row.GetCell(156));
                            workExperience.WorkExperience9StartTime = ConvertSheetData(row.GetCell(157));
                            workExperience.WorkExperience9EndTime = ConvertSheetData(row.GetCell(158));
                            workExperience.WorkExperience9CompanyName = ConvertSheetData(row.GetCell(159));
                            workExperience.WorkExperience9PositionName = ConvertSheetData(row.GetCell(160));
                            workExperience.WorkExperience9AnnualSalary = ConvertSheetData(row.GetCell(161));
                            workExperience.WorkExperience9IndustryCategory = ConvertSheetData(row.GetCell(162));
                            workExperience.WorkExperience9JobDescription = ConvertSheetData(row.GetCell(163));
                            workExperience.WorkExperience9Department = ConvertSheetData(row.GetCell(164));
                            workExperience.WorkExperience9ResignationReason = ConvertSheetData(row.GetCell(165));
                            workExperience.WorkExperience10StartTime = ConvertSheetData(row.GetCell(166));
                            workExperience.WorkExperience10EndTime = ConvertSheetData(row.GetCell(167));
                            workExperience.WorkExperience10CompanyName = ConvertSheetData(row.GetCell(168));
                            workExperience.WorkExperience10PositionName = ConvertSheetData(row.GetCell(169));
                            workExperience.WorkExperience10AnnualSalary = ConvertSheetData(row.GetCell(170));
                            workExperience.WorkExperience10IndustryCategory = ConvertSheetData(row.GetCell(171));
                            workExperience.WorkExperience10JobDescription = ConvertSheetData(row.GetCell(172));
                            workExperience.WorkExperience10Department = ConvertSheetData(row.GetCell(173));
                            workExperience.WorkExperience10ResignationReason = ConvertSheetData(row.GetCell(174));
                            workExperience.WorkExperience11StartTime = ConvertSheetData(row.GetCell(175));
                            workExperience.WorkExperience11EndTime = ConvertSheetData(row.GetCell(176));
                            workExperience.WorkExperience11CompanyName = ConvertSheetData(row.GetCell(177));
                            workExperience.WorkExperience11PositionName = ConvertSheetData(row.GetCell(178));
                            workExperience.WorkExperience11AnnualSalary = ConvertSheetData(row.GetCell(179));
                            workExperience.WorkExperience11IndustryCategory = ConvertSheetData(row.GetCell(180));
                            workExperience.WorkExperience11JobDescription = ConvertSheetData(row.GetCell(181));
                            workExperience.WorkExperience11Department = ConvertSheetData(row.GetCell(182));
                            workExperience.WorkExperience11ResignationReason = ConvertSheetData(row.GetCell(183));
                            workExperience.WorkExperience12StartTime = ConvertSheetData(row.GetCell(184));
                            workExperience.WorkExperience12EndTime = ConvertSheetData(row.GetCell(185));
                            workExperience.WorkExperience12CompanyName = ConvertSheetData(row.GetCell(186));
                            workExperience.WorkExperience12PositionName = ConvertSheetData(row.GetCell(187));
                            workExperience.WorkExperience12AnnualSalary = ConvertSheetData(row.GetCell(188));
                            workExperience.WorkExperience12IndustryCategory = ConvertSheetData(row.GetCell(189));
                            workExperience.WorkExperience12JobDescription = ConvertSheetData(row.GetCell(190));
                            workExperience.WorkExperience12Department = ConvertSheetData(row.GetCell(191));
                            workExperience.WorkExperience12ResignationReason = ConvertSheetData(row.GetCell(192));
                            resumeInfo.WorkExperience = JsonConvert.SerializeObject(workExperience);
                            #endregion
                            resumeInfo.ProjectExperience = ConvertSheetData(row.GetCell(193));
                            resumeInfo.TrainingExperience = ConvertSheetData(row.GetCell(194));
                            resumeInfo.EnglishAbility = ConvertSheetData(row.GetCell(195));
                            resumeInfo.ProfessionalSkills = ConvertSheetData(row.GetCell(196));
                            resumeInfo.Certificates = ConvertSheetData(row.GetCell(197));
                            resumeInfo.OriginalChannel = ConvertSheetData(row.GetCell(198));
                            resumeInfo.LatestChannel = ConvertSheetData(row.GetCell(199));
                            resumeInfo.Attachment1 = ConvertSheetData(row.GetCell(200));
                            resumeInfo.Attachment2 = ConvertSheetData(row.GetCell(201));
                            resumeInfo.Attachment3 = ConvertSheetData(row.GetCell(202));
                            resumeInfo.Attachment4 = ConvertSheetData(row.GetCell(203));
                            resumeInfo.Attachment5 = ConvertSheetData(row.GetCell(204));
                            resumeInfo.SyncDate = remoteFilePath;

                            if (!resumeInfo.Attachment1.IsNullOrEmpty())
                            {
                                if (replaceFiles.TryGetValue(resumeInfo.Attachment1, out string fileName))
                                {
                                    resumeInfo.Attachment1 = Path.GetFileName(fileName);
                                }
                            }

                            if (!resumeInfo.Attachment2.IsNullOrEmpty())
                            {
                                if (replaceFiles.TryGetValue(resumeInfo.Attachment2, out string fileName))
                                {
                                    resumeInfo.Attachment2 = Path.GetFileName(fileName);
                                }
                            }

                            if (!resumeInfo.Attachment3.IsNullOrEmpty())
                            {
                                if (replaceFiles.TryGetValue(resumeInfo.Attachment3, out string fileName))
                                {
                                    resumeInfo.Attachment3 = Path.GetFileName(fileName);
                                }
                            }

                            if (!resumeInfo.Attachment4.IsNullOrEmpty())
                            {
                                if (replaceFiles.TryGetValue(resumeInfo.Attachment4, out string fileName))
                                {
                                    resumeInfo.Attachment4 = Path.GetFileName(fileName);
                                }
                            }

                            if (!resumeInfo.Attachment5.IsNullOrEmpty())
                            {
                                if (replaceFiles.TryGetValue(resumeInfo.Attachment5, out string fileName))
                                {
                                    resumeInfo.Attachment5 = Path.GetFileName(fileName);
                                }
                            }

                            resumeInfos.Add(resumeInfo);
                        }


                        await _db.Insertable(resumeInfos).ExecuteCommandAsync();
                    }
                }
                catch (Exception ex)
                {
                    TalentProfileDescribeEntity talentProfileDescribeEntity = new TalentProfileDescribeEntity();
                    talentProfileDescribeEntity.Id = Guid.NewGuid().ToString();
                    talentProfileDescribeEntity.TalentProfileId = "OSSFailed";
                    talentProfileDescribeEntity.Content = $"从ftp同步文件失败，同步日期{remoteFilePath}，错误原因：{JsonConvert.SerializeObject(ex)}";
                    _db.Insertable(talentProfileDescribeEntity).ExecuteCommand();
                }
            }

            foreach (var file in replaceFiles)
            {
                UploadExcelToOSS(file.Value, remoteFilePath + "/" + Path.GetFileName(file.Value));
            }

            File.Delete(Path.Combine(_localFilePath, remoteFilePath)); // 解析完成之后将临时文件夹全部清空
        }

        public void UploadExcelToOSS(string localFilePath, string fileName)
        {
            var client = new OssClient(_endpoint, _accessKeyId, _accessKeySecret);
            try
            {
                // 检查Bucket是否存在
                bool bucketExists = client.DoesBucketExist(_bucketName);
                if (!bucketExists)
                {
                    Console.WriteLine($"Bucket '{_bucketName}' does not exist.");
                    return;
                }

                // 上传文件
                PutObjectResult result = client.PutObject(_bucketName, fileName, localFilePath);
                Console.WriteLine("File uploaded successfully.");
            }
            catch (OssException ex)
            {
                TalentProfileDescribeEntity talentProfileDescribeEntity = new TalentProfileDescribeEntity();
                talentProfileDescribeEntity.Id = Guid.NewGuid().ToString();
                talentProfileDescribeEntity.TalentProfileId = "OSSFailed";
                talentProfileDescribeEntity.Content = $"从ftp同步文件失败，同步日期{localFilePath}/{fileName}，错误原因：{JsonConvert.SerializeObject(ex)}";
                _db.Insertable(talentProfileDescribeEntity).ExecuteCommand();
            }
            catch (Exception ex)
            {
                TalentProfileDescribeEntity talentProfileDescribeEntity = new TalentProfileDescribeEntity();
                talentProfileDescribeEntity.Id = Guid.NewGuid().ToString();
                talentProfileDescribeEntity.TalentProfileId = "OSSFailed";
                talentProfileDescribeEntity.Content = $"从ftp同步文件失败，同步日期{localFilePath}/{fileName}，错误原因：{JsonConvert.SerializeObject(ex)}";
                _db.Insertable(talentProfileDescribeEntity).ExecuteCommand();
            }
        }

        /// <summary>
        /// 根据cell获取对应得值
        /// </summary>
        /// <param name="cell">cell</param>
        /// <returns>值</returns>
        private string ConvertSheetData(ICell cell)
        {
            try
            {
                switch (cell?.CellType)
                {
                    case CellType.String:
                        string tempStr = cell.StringCellValue;
                        // 定义一个正则表达式来匹配所有 emoji 字符
                        string pattern = @"[\uD83C-\uDBFF\uDC00-\uDFFF]+";

                        // 使用正则表达式移除 emoji
                        tempStr = Regex.Replace(tempStr, pattern, "", RegexOptions.Compiled | RegexOptions.IgnorePatternWhitespace);

                        // 使用正则表达式移除 制表符，空格符，换行符
                        return Regex.Replace(tempStr, @"\s+", "");
                    case CellType.Numeric:
                        return cell.NumericCellValue.ToString();
                    case CellType.Blank:
                        return string.Empty;
                    case CellType.Formula:
                        return cell.CachedFormulaResultType == CellType.String ? cell.StringCellValue : cell.NumericCellValue.ToString();
                    default:
                        return string.Empty;
                }
            }
            catch
            {
                return string.Empty;
            }
        }
    }
}